import 'package:sqlite3/sqlite3.dart';
import '../model/model_wordbook_word.dart';
import '../tools/tools_database.dart';

class DaoWordbookWord {
  static Future<int> insert(ModelWordbookWord l) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_wordbook_word(
        word,wordbook_id,
        gmt_create,gmt_modified,remark)
    VALUES
      (?,?,?,?
      ?,?,?)
    ;
    ''';
    final stmt = db.prepare(sql);
    DateTime now = DateTime.now();
    int gmt_create = now.millisecondsSinceEpoch;
    stmt..execute([l.word, l.wordbookId, gmt_create, gmt_create, l.remark]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> deleteById(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_wordbook_word 
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> updateById(ModelWordbookWord l) async {
    print(l);
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_wordbook_word
      set
        word=?,word_book_id=?,
        gmt_modified = ?,
        remark = ?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt..execute([l.word, l.wordbookId, gmt_modified, l.remark, l.id]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<List<ModelWordbookWord>> findAll() async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_wordbook_word
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelWordbookWord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelWordbookWord w = new ModelWordbookWord();
      w.id = r["id"];
      w.word = r["word"];
      w.wordbookId = r["wordbook_id"];

      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<ModelWordbookWord?> findById(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_wordbook_word
      where
        id = ?
      ;
    ''';
    ModelWordbookWord? w = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelWordbookWord();
      w.id = r["id"];
      w.word = r["word"];
      w.wordbookId = r["wordbook_id"];

      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return w;
  }
}
